from datetime import datetime
from string import Template
from tack.lib import string_app, json_app, content_headers, status_codes
from tack.lib.smtptools import message, send_message
from util import build_row_dict_for_sql, build_columns_values_sql, state_def


class Sql:
    select_details_by_commitment_id = """
    SELECT tcd.commitment_id,
           tcd.commitment_detail_id,
           tcd.state_start,
           tcd.state_finish,
           tcd.state_key,
           tcd.requested_by,
           tcd.requester_phone,
           tcd.trans_coord_name,
           tcd.trans_coord_phone,
           tcd.org_req_name,
           tcd.org_req_phone,
           tcd.purpose,
           tcd.poc_name,
           tcd.poc_phone,
           tcd.poc_alt_phone,
           tcd.poc_smtp,
           tcd.deliver_post,
           tcd.wait,
           tcd.fund_cite
    FROM tmpo_commitment_detail tcd
    WHERE tcd.commitment_id = $commitment_id
        AND tcd.state_key <> 4
        AND tcd.state_finish IS NULL
    """


    set_details_finish = """
    UPDATE tmpo_commitment_detail
    SET state_finish = '$time_stamp'
    WHERE commitment_detail_id = $commitment_detail_id_finish
    """


    insert_commitment_details = """
    INSERT INTO tmpo_commitment_detail
    """


    get_notify_message = """
    SELECT subject,
           markup 
    FROM message_template
    WHERE keyword = 'tmpo_not_canc'
    """



def delete_by_commitment_id(environ, start_response):
    commitment_id = environ['dispatcher.groups']['commitment_id']
    sql = Sql.select_details_by_commitment_id
    sql = Template(sql).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    if rows:
        commitment_detail_id_finish = rows[0].commitment_detail_id
        throwaway = datetime.now()
        time_stamp = throwaway.strftime('%Y-%m-%d %H:%M:%S')
        state_dict = build_row_dict_for_sql(cursor.description, rows[0])
        state_dict['state_start'] = "'" + time_stamp + "'"
        del state_dict['state_finish'], state_dict['commitment_detail_id']
        state_dict['state_key'] = str(state_def.CANCELED)
        sql = Sql.insert_commitment_details
        sql = sql + build_columns_values_sql(state_dict)
        cursor.execute(sql)
        environ['connection'].commit()
        sql = Sql.set_details_finish
        sql = Template(sql).substitute(locals())
        cursor.execute(sql)
        environ['connection'].commit()
        output = 'Canceled'    
    else:
        start_response(status_codes.not_found, content_headers.default_plain())
        return ['404 Not Found']
    return json_app(output)(environ, start_response)
    